#!/usr/bin/python2.7.5 
# -*- coding: utf-8 -*-
# 
# FileName : into_excel.py
# Author   : Fish
# DateTime : 2018/11/1 19:08

import MySQLdb
from DBUtils.PooledDB import PooledDB
import ConfigParser

conf_file_name = "Conf/config.ini"


class Mysql(object):

    __pool = None

    def __init__(self):
        self._conn = Mysql.__getConn()
        self._cursor = self._conn.cursor()

    def __getConn(self):
        if Mysql.__pool is None:
            #parse db configuration information
            cf = ConfigParser.ConfigParser()
            cf.read(conf_file_name)
            db_host = cf.get("db","db_host")
            db_port = int(cf.get("db","db_port"))
            db_user = cf.get("db","db_user")
            db_pwd = cf.get("db","db_password")
            db_name = cf.get("db","db_name")
            #create pool
            __pool = PooledDB(creator=MySQLdb, host=db_host, port=db_port, user=db_user, passwd=db_pwd, db=db_name)
        return __pool.connection()
    
    def getAll(self,sql,param=None):
        """
        @summary: execute query, and fetch all result
        @param sql: enquiry sql
        @param param:optional parameter
        @return:result list/boolean
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql,param)
        if count>0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result
        
    def getOne(self,sql,param=None):
        """
        @summary: execute query, and fetch one record back
        @param sql: enquiry sql
        @param param: optional parameter
        @return: result list
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql,param)
        if count>0:
            retsult = self._cursor.fetchone()
        else:
            result = False
        return result
        
    def __query(self,sql,param):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql,param)
        return count
        
    def update(self,sql,param):
        """
        @summary:udpdate record
        @param sql: update sql with formatting of (%s, %s)
        @param param: update value, tuple/list
        @return: count, which update sql impacted
        """
        return self.__query(sql,param)
        
    def delete(self,sql,param=None):
        """
        @summary:delete record
        @param sql: delete sql with formatting of (%s, %s)
        @param param: the condition which wants to delete, tuple/list
        @return: count which delete query impacts
        """
        return self.__query(sql,param)
        
    def begin(self):
        """
        @summary:start transaction
        """
        return self._conn.autocommit(0)
        
    def end(self,option='commit'):
        """
        @summary:end transaction
        """
        if option=='commit':
            self._conn.commit()
        else:
            self._conn.rollback()
        
    def dispose(self,isEnd=1):
        """
        @summary:release connection
        """
        if isEnd==1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()
    
    def close(self):
        """
        @summary:close connection for inquery
        """
        self._cursor.close()
        self._conn.close()